﻿using Avin.Infrastructure.Entity;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace Avin.Infrastructure.Linq.ToDbScript
{
    public class ToSqlServerScript : IToDbScript
    {
        public string Insert<T>(T entity, string[] ignoreFields, List<DbParameter> parameters) where T : class
        {
            StringBuilder sbName = new();
            StringBuilder sbValue = new();

            var propertyColumnMapping = EntityInitializer.GenerateTypePropertyMapping(typeof(T));
            foreach (var pa in propertyColumnMapping)
            {
                if (ignoreFields.Contains(pa.Key))
                    continue;

                var obj = pa.Value.PropertyInfo.GetValue(entity, null);
                var param = SqlClientFactory.Instance.CreateParameter();
                if (param != null)
                {
                    param.ParameterName = "@" + pa.Key;
                    param.Value = obj ?? DBNull.Value;
                    parameters.Add(param);
                    sbName.Append(EntityInitializer.GetColumnName(SqlClientFactory.Instance, typeof(T), pa.Key) + ",");
                    sbValue.Append("@" + pa.Key + ",");
                }
            }
            if (sbName.Length > 0)
            {
                sbName = sbName.Remove(sbName.Length - 1, 1);
                sbValue = sbValue.Remove(sbValue.Length - 1, 1);
            }

            return "insert into " + EntityInitializer.GetTableName(SqlClientFactory.Instance, typeof(T)) + " (" + sbName.ToString() + ") values (" + sbValue.ToString() + ")"; ;
        }

        public string Update<T>(T entity, string[] ignoreFields, List<DbParameter> parameters) where T : class
        {
            StringBuilder sb = new("update " + EntityInitializer.GetTableName(SqlClientFactory.Instance, typeof(T)) + " set ");
            var isFirst = true;
            var propertyColumnMapping = EntityInitializer.GenerateTypePropertyMapping(typeof(T));
            foreach (var pa in propertyColumnMapping)
            {
                if (ignoreFields.Contains(pa.Key))
                    continue;

                if (isFirst)
                    isFirst = false;
                else
                    sb.Append(',');

                sb.Append(EntityInitializer.GetColumnName(SqlClientFactory.Instance, typeof(T), pa.Key) + " = @" + pa.Key);
                if (!parameters.Any(a => a.ParameterName == "@" + pa.Key))
                {
                    object obj = pa.Value.PropertyInfo.GetValue(entity, null);
                    DbParameter parameter = SqlClientFactory.Instance.CreateParameter();
                    parameter.ParameterName = "@" + pa.Key;
                    parameter.Value = obj ?? DBNull.Value;
                    parameters.Add(parameter);
                }
            }
            return sb.ToString();
        }

        public string Update2<T>(T entity, string[] updateFields, List<DbParameter> parameters) where T : class
        {
            StringBuilder sb = new("update " + EntityInitializer.GetTableName(SqlClientFactory.Instance, typeof(T)) + " set ");
            var isFirst = true;
            var propertyColumnMapping = EntityInitializer.GenerateTypePropertyMapping(typeof(T));
            foreach (var pa in propertyColumnMapping)
            {
                if (!updateFields.Contains(pa.Key))
                    continue;

                if (isFirst)
                    isFirst = false;
                else
                    sb.Append(',');

                sb.Append(EntityInitializer.GetColumnName(SqlClientFactory.Instance, typeof(T), pa.Key) + " = @" + pa.Key);
                if (!parameters.Any(a => a.ParameterName == "@" + pa.Key))
                {
                    object obj = pa.Value.PropertyInfo.GetValue(entity, null);
                    DbParameter parameter = SqlClientFactory.Instance.CreateParameter();
                    parameter.ParameterName = "@" + pa.Key;
                    parameter.Value = obj ?? DBNull.Value;
                    parameters.Add(parameter);
                }
            }
            return sb.ToString();
        }

        public string Delete<T>() where T : class
        {
            return "delete from " + EntityInitializer.GetTableName(SqlClientFactory.Instance, typeof(T));
        }

        public string SelectTop(QueryInfo queryInfo, uint count)
        {
            StringBuilder sb = new();
            sb.Append("select top " + count + " ");

            if (queryInfo.SelectInfos == null || queryInfo.SelectInfos.Count == 0)
            {
                sb.Append("* ");
            }
            else
            {
                for (var i = 0; i < queryInfo.SelectInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');
                    sb.Append(queryInfo.SelectInfos[i].FieldName + (string.IsNullOrWhiteSpace(queryInfo.SelectInfos[i].OtherName) ? "" : " as " + queryInfo.SelectInfos[i].OtherName));
                }
                sb.Append(' ');
            }

            if (queryInfo.TableInfos != null && queryInfo.TableInfos.Count > 0)
            {
                for (var i = 0; i < queryInfo.TableInfos.Count; i++)
                {
                    if (i == 0)
                        sb.Append("from " + queryInfo.TableInfos[i].SourceName + " as " + queryInfo.TableInfos[i].OtherName + " ");

                    else
                        sb.Append(queryInfo.TableInfos[i].JoinType + " " + queryInfo.TableInfos[i].SourceName + " as " + queryInfo.TableInfos[i].OtherName + " " + queryInfo.TableInfos[i].Where + " ");
                }
            }

            if (queryInfo.Where.Length > 0)
            {
                sb.Append("where " + queryInfo.Where.ToString());
                sb.Append(' ');
            }

            if (queryInfo.GroupByInfos != null && queryInfo.GroupByInfos.Count > 0)
            {
                sb.Append("group by ");
                for (var i = 0; i < queryInfo.GroupByInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');
                    sb.Append(queryInfo.GroupByInfos[i].FieldName);
                }
                sb.Append(' ');
            }

            if (queryInfo.OrderByInfos != null && queryInfo.OrderByInfos.Count > 0)
            {
                sb.Append("order by ");
                for (var i = 0; i < queryInfo.OrderByInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');

                    sb.Append(queryInfo.OrderByInfos[i].FieldName + " " + queryInfo.OrderByInfos[i].OrderBy);
                }
            }

            return sb.ToString();
        }

        public string SelectTake(QueryInfo queryInfo)
        {
            StringBuilder sb = new();
            sb.Append("select top " + (queryInfo.TakeInfo.Skip + queryInfo.TakeInfo.Take) + " ");

            if (queryInfo.SelectInfos == null || queryInfo.SelectInfos.Count == 0)
            {
                sb.Append('*');
            }
            else
            {
                for (var i = 0; i < queryInfo.SelectInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');
                    sb.Append(queryInfo.SelectInfos[i].FieldName + (string.IsNullOrWhiteSpace(queryInfo.SelectInfos[i].OtherName) ? "" : " as " + queryInfo.SelectInfos[i].OtherName));
                }
            }

            if (queryInfo.OrderByInfos != null && queryInfo.OrderByInfos.Count > 0)
            {
                sb.Append(",row_number() over(order by ");
                for (var i = 0; i < queryInfo.OrderByInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');

                    sb.Append(queryInfo.OrderByInfos[i].FieldName + " " + queryInfo.OrderByInfos[i].OrderBy);
                }
                sb.Append(") as row_index ");
            }
            else
            {
                sb.Append(",row_number() over(order by Id desc) as row_index ");
            }

            if (queryInfo.TableInfos != null && queryInfo.TableInfos.Count > 0)
            {
                for (var i = 0; i < queryInfo.TableInfos.Count; i++)
                {
                    if (i == 0)
                        sb.Append("from " + queryInfo.TableInfos[i].SourceName + " as " + queryInfo.TableInfos[i].OtherName + " ");

                    else
                        sb.Append(queryInfo.TableInfos[i].JoinType + " " + queryInfo.TableInfos[i].SourceName + " as " + queryInfo.TableInfos[i].OtherName + " " + queryInfo.TableInfos[i].Where + " ");
                }
            }

            if (queryInfo.Where.Length > 0)
            {
                sb.Append("where " + queryInfo.Where.ToString());
                sb.Append(' ');
            }

            if (queryInfo.GroupByInfos != null && queryInfo.GroupByInfos.Count > 0)
            {
                sb.Append("group by ");
                for (var i = 0; i < queryInfo.GroupByInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');
                    sb.Append(queryInfo.GroupByInfos[i].FieldName);
                }
                sb.Append(' ');
            }

            return "select t1t2t3.* from(" + sb.ToString() + ") as t1t2t3 where t1t2t3.row_index > " + queryInfo.TakeInfo.Skip;
        }

        public string Select(QueryInfo queryInfo)
        {
            StringBuilder sb = new();
            sb.Append("select ");

            if (queryInfo.SelectInfos == null || queryInfo.SelectInfos.Count == 0)
            {
                sb.Append("* ");
            }
            else
            {
                for (var i = 0; i < queryInfo.SelectInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');
                    sb.Append(queryInfo.SelectInfos[i].FieldName + (string.IsNullOrWhiteSpace(queryInfo.SelectInfos[i].OtherName) ? "" : " as " + queryInfo.SelectInfos[i].OtherName));
                }
                sb.Append(' ');
            }

            if (queryInfo.TableInfos != null && queryInfo.TableInfos.Count > 0)
            {
                for (var i = 0; i < queryInfo.TableInfos.Count; i++)
                {
                    if (i == 0)
                        sb.Append("from " + queryInfo.TableInfos[i].SourceName + " as " + queryInfo.TableInfos[i].OtherName + " ");

                    else
                        sb.Append(queryInfo.TableInfos[i].JoinType + " " + queryInfo.TableInfos[i].SourceName + " as " + queryInfo.TableInfos[i].OtherName + " " + queryInfo.TableInfos[i].Where + " ");
                }
            }

            if (queryInfo.Where.Length > 0)
            {
                sb.Append("where " + queryInfo.Where.ToString());
                sb.Append(' ');
            }

            if (queryInfo.GroupByInfos != null && queryInfo.GroupByInfos.Count > 0)
            {
                sb.Append("group by ");
                for (var i = 0; i < queryInfo.GroupByInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');
                    sb.Append(queryInfo.GroupByInfos[i].FieldName);
                }
                sb.Append(' ');
            }

            if (queryInfo.OrderByInfos != null && queryInfo.OrderByInfos.Count > 0)
            {
                sb.Append("order by ");
                for (var i = 0; i < queryInfo.OrderByInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');

                    sb.Append(queryInfo.OrderByInfos[i].FieldName + " " + queryInfo.OrderByInfos[i].OrderBy);
                }
            }

            return sb.ToString();
        }

        public string Count(QueryInfo queryInfo)
        {
            StringBuilder sb = new();
            sb.Append("select count(1) ");

            if (queryInfo.TableInfos != null && queryInfo.TableInfos.Count > 0)
            {
                for (var i = 0; i < queryInfo.TableInfos.Count; i++)
                {
                    if (i == 0)
                        sb.Append("from " + queryInfo.TableInfos[i].SourceName + " as " + queryInfo.TableInfos[i].OtherName + " ");

                    else
                        sb.Append(queryInfo.TableInfos[i].JoinType + " " + queryInfo.TableInfos[i].SourceName + " as " + queryInfo.TableInfos[i].OtherName + " " + queryInfo.TableInfos[i].Where + " ");
                }
            }

            if (queryInfo.Where.Length > 0)
            {
                sb.Append("where " + queryInfo.Where.ToString());
                sb.Append(' ');
            }

            if (queryInfo.GroupByInfos != null && queryInfo.GroupByInfos.Count > 0)
            {
                sb.Append("group by ");
                for (var i = 0; i < queryInfo.GroupByInfos.Count; i++)
                {
                    if (i > 0)
                        sb.Append(',');
                    sb.Append(queryInfo.GroupByInfos[i].FieldName);
                }
                sb.Append(' ');

                return "select count(1) from (" + sb.ToString() + ")";
            }

            return sb.ToString();
        }
    }
}
